package com.starkuz.dao.impl;


import com.starkuz.dao.ifaces.leads.LeadDao;
import com.starkuz.model.Lead;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class LeadDaoImpl implements LeadDao {

    private static final String INSERT_LEAD = "insert into leads (siteId, email, dateMade) values (?, ?, ?)";
    private static final String SELECT_ALL_LEADS = "select * from leads";
    private static final String SELECT_ALL_LEADS_BY_SITE_ID = "select * from leads where siteId = ?";
    private static final String SELECT_ALL_LEADS_BY_USER_ID = "join!!!";  //todo: реализовать джойн
    private static final String COUNT_ALL_LEADS_BY_SITE_ID = "select count(*) value from leads where siteId = ?";
    private static final String COUNT_ALL_LEADS_BY_USER_ID = "join!!!";   //todo: реализовать джойн

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public void addLead(Integer siteId, String email) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con =  ds.getConnection();
            psql = con.prepareStatement(INSERT_LEAD);
            psql.setInt(1, siteId);
            psql.setString(2, email);
            psql.setDate(3, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Lead> getAllLeads() {
        List<Lead> leads = null;
        Connection con = null;
        Statement st = null;
        ResultSet res = null;
        try {
            con = ds.getConnection();
            st = con.createStatement();
            res = st.executeQuery(SELECT_ALL_LEADS);
            leads = resultSetToLeadsList(res);
            return leads;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                res.close();
                st.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<Lead>();
    }


    @Override
    public List<Lead> getAllLeadsBySiteId(Integer siteId) {
        List<Lead> leads = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LEADS_BY_SITE_ID);
            psql.setInt(1, siteId);
            leads = resultSetToLeadsList(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return leads;
    }

    @Override
    public List<Lead> getAllLeadsByUserId(Integer userId) {
        List<Lead> leads = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LEADS_BY_USER_ID);
            psql.setInt(1, userId);
            leads = resultSetToLeadsList(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return leads;
    }

    @Override
    public Integer countLeadBySiteId(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_LEADS_BY_SITE_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    @Override
    public Integer countLeadByUserId(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_LEADS_BY_USER_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    private List<Lead> resultSetToLeadsList(ResultSet rs) throws SQLException {
        List<Lead> leads = new ArrayList<Lead>();
        while (rs.next()) {
            Lead lead = new Lead();
            lead.setId(rs.getInt("id"));
            lead.setSiteId(rs.getInt("siteId"));
            lead.setEmail(rs.getString("email"));
            lead.setDateMade(rs.getDate("dateMade"));

            leads.add(lead);
        }
        return leads;
    }
}
